package com.example.huadu_server.house.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.huadu_server.house.entity.House;
import com.example.huadu_server.landlord.entity.Landlord;
import org.apache.ibatis.annotations.*;

import java.sql.Timestamp;
import java.util.List;
import java.util.Map;

@Mapper
public interface HouseMapper extends BaseMapper<House> {

    /**
     * 搜索房源（全文搜索）
     */
    @Select("<script>" +
            "SELECT h.*, " +
            "       (SELECT GROUP_CONCAT(image_url) " +
            "        FROM house_images " +
            "        WHERE house_id = h.id) AS imageUrls " +
            "FROM house h " +
            "WHERE h.house_status = 'available' " +  // 添加状态筛选条件
            "<if test='keyword != null and keyword != \"\"'>" +
            "  AND MATCH(h.title, h.description, h.address, h.house_type, h.business_area, h.subway_station, h.community, h.facilities) " +
            "      AGAINST(#{keyword} IN BOOLEAN MODE)" +
            "</if>" +
            "ORDER BY h.time DESC " +
            "LIMIT #{pageSize} OFFSET #{offset}" +
            "</script>")
    List<House> searchHousesByKeyword(@Param("keyword") String keyword,
                                      @Param("offset") int offset,
                                      @Param("pageSize") int pageSize);

    /**
     * 获取全文搜索的总数
     */
    @Select("<script>" +
            "SELECT COUNT(*) FROM house h " +
            "WHERE h.house_status = 'available' " +
            "<if test='keyword != null and keyword != \"\"'>" +
            "  AND MATCH(h.title, h.description, h.address, h.house_type, h.business_area, h.subway_station, h.community, h.facilities) " +
            "      AGAINST(#{keyword} IN BOOLEAN MODE)" +
            "</if>" +
            "</script>")
    int getSearchCount(@Param("keyword") String keyword);


    /**
     * 模糊搜索房源
     */
    @Select("<script>" +
            "SELECT h.*, " +
            "       (SELECT GROUP_CONCAT(image_url) " +
            "        FROM house_images " +
            "        WHERE house_id = h.id) AS imageUrls " +
            "FROM house h " +
            "WHERE h.title LIKE CONCAT('%', #{keyword}, '%') " +
            "   OR h.description LIKE CONCAT('%', #{keyword}, '%') " +
            "   OR h.address LIKE CONCAT('%', #{keyword}, '%') " +
            "   OR h.community LIKE CONCAT('%', #{keyword}, '%') " +
            "   OR h.business_area LIKE CONCAT('%', #{keyword}, '%') " +
            "   OR h.house_type LIKE CONCAT('%', #{keyword}, '%') " +
            "   OR h.facilities LIKE CONCAT('%', #{keyword}, '%') " +
            "   OR h.subway_station LIKE CONCAT('%', #{keyword}, '%') " +
            "ORDER BY h.time DESC " +
            "LIMIT #{pageSize} OFFSET #{offset}" +
            "</script>")
    List<House> searchHousesFallback(@Param("keyword") String keyword,
                                     @Param("offset") int offset,
                                     @Param("pageSize") int pageSize);

    /**
     * 获取模糊搜索的总数
     */
    @Select("<script>" +
            "SELECT COUNT(*) FROM house h " +
            "WHERE h.house_status = 'available' " +
            "  AND (h.title LIKE CONCAT('%', #{keyword}, '%') " +
            "       OR h.description LIKE CONCAT('%', #{keyword}, '%') " +
            "       OR h.address LIKE CONCAT('%', #{keyword}, '%') " +
            "       OR h.community LIKE CONCAT('%', #{keyword}, '%') " +
            "       OR h.business_area LIKE CONCAT('%', #{keyword}, '%') " +
            "       OR h.house_type LIKE CONCAT('%', #{keyword}, '%') " +
            "       OR h.facilities LIKE CONCAT('%', #{keyword}, '%') " +
            "       OR h.subway_station LIKE CONCAT('%', #{keyword}, '%'))" +
            "</script>")
    int getFallbackSearchCount(@Param("keyword") String keyword);

    @Select("<script>" +
            "SELECT h.*, " +
            "       (SELECT GROUP_CONCAT(image_url) " +
            "        FROM house_images " +
            "        WHERE house_id = h.id) AS imageUrls " +
            "FROM house h " +
            "WHERE 1=1 " +
            "<if test='filters.houseType != null and filters.houseType != \"\"'> " +
            "   AND (" +
            "       <foreach collection='filters.houseType.split(\",\")' item='type' separator=' OR '>" +
            "           h.house_type LIKE #{type} " +
            "       </foreach>" +
            "   ) " +
            "</if> " +
            "<if test='filters.areaMin != null'> " +
            "   AND h.area &gt;= #{filters.areaMin} " +
            "</if> " +
            "<if test='filters.areaMax != null'> " +
            "   AND h.area &lt;= #{filters.areaMax} " +
            "</if> " +
            "<if test='filters.priceMin != null'> " +
            "   AND h.price &gt;= #{filters.priceMin} " +
            "</if> " +
            "<if test='filters.priceMax != null'> " +
            "   AND h.price &lt;= #{filters.priceMax} " +
            "</if> " +
            "<if test='filters.floorMin != null'> " +
            "   AND h.floor &gt;= #{filters.floorMin} " +
            "</if> " +
            "<if test='filters.floorMax != null'> " +
            "   AND h.floor &lt;= #{filters.floorMax} " +
            "</if> " +
            "<if test='filters.businessArea != null and filters.businessArea != \"\"'> " +
            "   AND h.business_area = #{filters.businessArea} " +
            "</if> " +
            "<if test='filters.subwayStation != null and filters.subwayStation != \"\"'> " +
            "   AND h.subway_station = #{filters.subwayStation} " +
            "</if> " +
            "GROUP BY h.id " +
            "ORDER BY MAX(h.time) DESC " +
            "LIMIT #{pageSize} OFFSET #{offset}" +
            "</script>")
    List<House> filterHouses(@Param("filters") Map<String, Object> filters,
                             @Param("offset") int offset,
                             @Param("pageSize") int pageSize);

    @Select("<script>" +
            "SELECT COUNT(*) " +
            "FROM house h " +
            "WHERE 1=1 " +
            "<if test='filters.houseType != null and filters.houseType != \"\"'> " +
            "   AND (" +
            "       <foreach collection='filters.houseType.split(\",\")' item='type' separator=' OR '>" +
            "           h.house_type LIKE #{type} " +
            "       </foreach>" +
            "   ) " +
            "</if> " +
            "<if test='filters.areaMin != null'> " +
            "   AND h.area &gt;= #{filters.areaMin} " +
            "</if> " +
            "<if test='filters.areaMax != null'> " +
            "   AND h.area &lt;= #{filters.areaMax} " +
            "</if> " +
            "<if test='filters.priceMin != null'> " +
            "   AND h.price &gt;= #{filters.priceMin} " +
            "</if> " +
            "<if test='filters.priceMax != null'> " +
            "   AND h.price &lt;= #{filters.priceMax} " +
            "</if> " +
            "<if test='filters.floorMin != null'> " +
            "   AND h.floor &gt;= #{filters.floorMin} " +
            "</if> " +
            "<if test='filters.floorMax != null'> " +
            "   AND h.floor &lt;= #{filters.floorMax} " +
            "</if> " +
            "<if test='filters.businessArea != null and filters.businessArea != \"\"'> " +
            "   AND h.business_area = #{filters.businessArea} " +
            "</if> " +
            "<if test='filters.subwayStation != null and filters.subwayStation != \"\"'> " +
            "   AND h.subway_station = #{filters.subwayStation} " +
            "</if> " +
            "</script>")
    int getFilterCount(@Param("filters") Map<String, Object> filters);

    @Select("<script>" +
            "SELECT h.*, " +
            "       (SELECT GROUP_CONCAT(image_url) " +
            "        FROM house_images " +
            "        WHERE house_id = h.id) AS imageUrls " +
            "FROM house h " +
            "WHERE 1=1 " +
            "<if test='keyword != null and keyword != \"\"'>" +
            "   AND (h.title LIKE CONCAT('%', #{keyword}, '%') " +
            "       OR h.description LIKE CONCAT('%', #{keyword}, '%') " +
            "       OR h.address LIKE CONCAT('%', #{keyword}, '%') " +
            "       OR h.house_type LIKE CONCAT('%', #{keyword}, '%') " +
            "       OR h.business_area LIKE CONCAT('%', #{keyword}, '%') " +
            "       OR h.subway_station LIKE CONCAT('%', #{keyword}, '%') " +
            "       OR h.community LIKE CONCAT('%', #{keyword}, '%') " +
            "       OR h.facilities LIKE CONCAT('%', #{keyword}, '%'))" +
            "</if>" +
            "<if test='filters != null'>" +
            "   <if test='filters.houseType != null and filters.houseType != \"\"'> " +
            "   AND (" +
            "       <foreach collection='filters.houseType.split(\",\")' item='type' separator=' OR '>" +
            "           h.house_type LIKE #{type} " +
            "       </foreach>" +
            "   ) " +
            "   </if> " +
            "   <if test='filters.areaMin != null'>" +
            "       AND h.area &gt;= #{filters.areaMin}" +
            "   </if>" +
            "   <if test='filters.areaMax != null'>" +
            "       AND h.area &lt;= #{filters.areaMax}" +
            "   </if>" +
            "   <if test='filters.priceMin != null'>" +
            "       AND h.price &gt;= #{filters.priceMin}" +
            "   </if>" +
            "   <if test='filters.priceMax != null'>" +
            "       AND h.price &lt;= #{filters.priceMax}" +
            "   </if>" +
            "   <if test='filters.floorMin != null'>" +
            "       AND h.floor &gt;= #{filters.floorMin}" +
            "   </if>" +
            "   <if test='filters.floorMax != null'>" +
            "       AND h.floor &lt;= #{filters.floorMax}" +
            "   </if>" +
            "   <if test='filters.businessArea != null and filters.businessArea != \"\"'>" +
            "       AND h.business_area = #{filters.businessArea}" +
            "   </if>" +
            "   <if test='filters.subwayStation != null and filters.subwayStation != \"\"'>" +
            "       AND h.subway_station = #{filters.subwayStation}" +
            "   </if>" +
            "</if>" +
            "ORDER BY h.time DESC " +
            "LIMIT #{pageSize} OFFSET #{offset}" +
            "</script>")
    List<House> searchAndFilterHouses(@Param("keyword") String keyword,
                                      @Param("filters") Map<String, Object> filters,
                                      @Param("offset") int offset,
                                      @Param("pageSize") int pageSize);

    @Select("<script>" +
            "SELECT COUNT(*) " +
            "FROM house h " +
            "WHERE 1=1 " +
            "<if test='keyword != null and keyword != \"\"'>" +
            "   AND (h.title LIKE CONCAT('%', #{keyword}, '%') " +
            "       OR h.description LIKE CONCAT('%', #{keyword}, '%') " +
            "       OR h.address LIKE CONCAT('%', #{keyword}, '%') " +
            "       OR h.house_type LIKE CONCAT('%', #{keyword}, '%') " +
            "       OR h.business_area LIKE CONCAT('%', #{keyword}, '%') " +
            "       OR h.subway_station LIKE CONCAT('%', #{keyword}, '%') " +
            "       OR h.community LIKE CONCAT('%', #{keyword}, '%') " +
            "       OR h.facilities LIKE CONCAT('%', #{keyword}, '%'))" +
            "</if>" +
            "<if test='filters != null'>" +
            "   <if test='filters.houseType != null and filters.houseType != \"\"'> " +
            "   AND (" +
            "       <foreach collection='filters.houseType.split(\",\")' item='type' separator=' OR '>" +
            "           h.house_type LIKE #{type} " +
            "       </foreach>" +
            "   ) " +
            "   </if> " +
            "   <if test='filters.areaMin != null'>" +
            "       AND h.area &gt;= #{filters.areaMin}" +
            "   </if>" +
            "   <if test='filters.areaMax != null'>" +
            "       AND h.area &lt;= #{filters.areaMax}" +
            "   </if>" +
            "   <if test='filters.priceMin != null'>" +
            "       AND h.price &gt;= #{filters.priceMin}" +
            "   </if>" +
            "   <if test='filters.priceMax != null'>" +
            "       AND h.price &lt;= #{filters.priceMax}" +
            "   </if>" +
            "   <if test='filters.floorMin != null'>" +
            "       AND h.floor &gt;= #{filters.floorMin}" +
            "   </if>" +
            "   <if test='filters.floorMax != null'>" +
            "       AND h.floor &lt;= #{filters.floorMax}" +
            "   </if>" +
            "   <if test='filters.businessArea != null and filters.businessArea != \"\"'>" +
            "       AND h.business_area = #{filters.businessArea}" +
            "   </if>" +
            "   <if test='filters.subwayStation != null and filters.subwayStation != \"\"'>" +
            "       AND h.subway_station = #{filters.subwayStation}" +
            "   </if>" +
            "</if>" +
            "</script>")
    int getSearchAndFilterCount(@Param("keyword") String keyword,
                                @Param("filters") Map<String, Object> filters);

    /**
     * 查询所有房源
     */
    @Select("<script>" +
            "SELECT h.*, " +
            "       (SELECT GROUP_CONCAT(image_url) " +
            "        FROM house_images " +
            "        WHERE house_id = h.id) AS imageUrls " +
            "FROM house h " +
            "WHERE h.house_status = #{status} " +  // 动态传入房源状态
            "ORDER BY h.time DESC " +
            "LIMIT #{pageSize} OFFSET #{offset}" +
            "</script>")
    List<House> getHousesByPage(@Param("offset") int offset,
                                @Param("pageSize") int pageSize,
                                @Param("status") String status);  // 动态传入房源状态


    /**
     * 查询所有房源
     *
     * @return
     */
    @Select("<script>" +
            "SELECT COUNT(*) FROM (" +
            "   SELECT h.* " +
            "   FROM house h " +
            "   ORDER BY h.time DESC " +
            ") AS filteredHouses" +
            "</script>")
    int getFilteredTotalHouses();

    /**
     * 根据房源的房东ID获取房东信息,通过房源ID查到房东ID，然后将房东ID在房东表查出房东
     *
     * @param houseId 房源ID
     * @return
     */
    /**
     * 根据房源ID获取房东信息
     *
     * @param houseId 房源ID
     * @return Landlord 房东信息
     */
    @Select("SELECT l.* FROM landlord l " +
            "JOIN house h ON h.landlord_id = l.id " +
            "WHERE h.id = #{houseId}")
    Landlord getLandlordByHouseId(@Param("houseId") int houseId);

    /**
     * 根据房源ID集合获取房源信息
     *
     * @param ids
     * @return
     */
    @Select("<script>" +
            "SELECT * FROM house h WHERE h.house_status = 'available' AND h.id IN " +
            "<foreach item='id' collection='ids' open='(' separator=',' close=')'>" +
            "#{id}" +
            "</foreach>" +
            "</script>")
    List<House> getHousesByIds(@Param("ids") List<Integer> ids);

    /**
     * 根据房源属性查询房源
     *
     * @param houseType
     * @param subwayStation
     * @param area
     * @param price
     * @param businessArea
     * @param limit
     * @return
     */
    @Select("<script>" +
            "SELECT * FROM house " +
            "WHERE house_status = 'approved' " +  // 只返回审核通过的房源
            "AND house_status = #{status} " +  // 动态选择房源状态
            "AND house_type = #{houseType} " +
            "AND subway_station = #{subwayStation} " +
            "AND business_area = #{businessArea} " +
            "AND ABS(area - #{area}) &lt;= 20 " +    // 放宽面积容差
            "AND ABS(price - #{price}) &lt;= 1000 " +  // 放宽租金容差
            "ORDER BY time DESC " +
            "LIMIT #{limit}" +
            "</script>")
    List<House> getHousesByAttributes(@Param("houseType") String houseType,
                                      @Param("subwayStation") String subwayStation,
                                      @Param("area") Double area,
                                      @Param("price") Double price,
                                      @Param("businessArea") String businessArea,
                                      @Param("status") String status, // 动态传入房源状态
                                      @Param("limit") int limit);


    /**
     * 根据用户ID获取收藏的房源
     *
     * @param userId
     * @param offset
     * @param pageSize
     * @return
     */
    @Select("SELECT h.* " +
            "FROM favority f " +
            "JOIN house h ON f.house_id = h.id " +
            "WHERE f.user_id = #{userId} " +
            "ORDER BY f.time DESC " +
            "LIMIT #{pageSize} OFFSET #{offset}")
    List<House> getFavoriteHouses(@Param("userId") int userId,
                                  @Param("offset") int offset,
                                  @Param("pageSize") int pageSize);

    /**
     * 根据用户ID获取收藏的房源总数
     *
     * @param userId
     * @return
     */
    @Select("SELECT COUNT(*) " +
            "FROM favority f " +
            "WHERE f.user_id = #{userId}")
    int getFavoriteCount(@Param("userId") int userId);


    @Select("<script>" +
            "SELECT * FROM house " +
            "WHERE landlord_id = #{landlordId} " +
            "<if test='status != null and status != \"\" and status != \"all\"'>" +
            "AND house_status = #{status} " +
            "</if> " +
            "ORDER BY id DESC " +
            "LIMIT #{offset}, #{pageSize}" +
            "</script>")
    List<House> getHousesByLandlordId(@Param("landlordId") int landlordId,
                                      @Param("offset") int offset,
                                      @Param("pageSize") int pageSize,
                                      @Param("status") String status);

    @Select("<script>" +
            "SELECT COUNT(*) FROM house " +
            "WHERE landlord_id = #{landlordId} " +
            "<if test='status != null and status != \"\" and status != \"all\"'>" +
            "AND house_status = #{status} " +
            "</if>" +
            "</script>")
    int getHouseCountByLandlordId(@Param("landlordId") int landlordId,
                                  @Param("status") String status);


    /**
     * 更新房源
     *
     * @param house
     * @return
     */
    @Update("UPDATE house SET title = #{title}, description = #{description}, community = #{community}, " +
            "address = #{address}, house_type = #{houseType}, facilities = #{facilities}, price = #{price}, " +
            "area = #{area}, floor = #{floor}, business_area = #{businessArea}, subway_station = #{subwayStation}, " +
            "lease = #{lease}, pay_type = #{payType}, traffic = #{traffic}, longitude = #{longitude}, latitude = #{latitude}, " +
            "house_status = #{houseStatus}, remarks = #{remarks}, title_deed = #{titleDeed}  " +
            "WHERE id = #{id}")
    int updateHouse(House house);


    /**
     * 更新房源状态
     *
     * @param houseId
     * @param newStatus
     * @return
     */
    @Update("UPDATE house SET house_status = #{newStatus} WHERE id = #{houseId}")
    int updateHouseStatus(@Param("houseId") int houseId, @Param("newStatus") String newStatus);

    @Select("SELECT * FROM house WHERE id = #{id}")
    House getHouseById(@Param("id") int id);

    /**
     * 尝试锁定房源
     *
     * @param houseId
     * @param adminId
     * @param lockTime
     * @param timeout
     * @return
     */
    @Update("UPDATE house SET locked_by = #{adminId}, lock_time = #{lockTime} " +
            "WHERE id = #{houseId} AND (" +
            "   locked_by IS NULL " +
            "   OR TIMESTAMPDIFF(MINUTE, lock_time, NOW()) >= #{timeout} " +
            "   OR locked_by = #{adminId}" +  // 新增条件：允许同一管理员重新获取锁
            ")")
    int lockHouse(@Param("houseId") int houseId,
                  @Param("adminId") int adminId,
                  @Param("lockTime") Timestamp lockTime,
                  @Param("timeout") long timeout);


    /**
     * 审核房源并添加管理员ID
     *
     * @param houseId
     * @param newStatus
     * @param remarks
     * @param adminId
     * @return
     */
    @Update("UPDATE house SET house_status = #{newStatus}, remarks = #{remarks}, admin_id = #{adminId} " +
            "WHERE id = #{houseId} AND house_status = 'pending'")
    int auditHouseByAdmin(@Param("houseId") int houseId,
                          @Param("newStatus") String newStatus,
                          @Param("remarks") String remarks,
                          @Param("adminId") int adminId);

    /**
     * 删除房源并添加管理员ID
     * @param houseId
     * @param newStatus
     * @param remarks
     * @param adminId
     * @param requiredStatus
     * @return
     */
    @Update("UPDATE house SET house_status = #{newStatus}, remarks = #{remarks}, admin_id = #{adminId} " +
            "WHERE id = #{houseId} AND house_status = #{requiredStatus}")
    int removeHouseByAdmin(@Param("houseId") int houseId,
                           @Param("newStatus") String newStatus,
                           @Param("remarks") String remarks,
                           @Param("adminId") int adminId,
                           @Param("requiredStatus") String requiredStatus);  // 添加 requiredStatus 参数

    /**
     * 查询房源趋势统计
     * @param startDate
     * @param endDate
     * @param granularity
     * @return
     */
    @Select("<script>" +
            "SELECT DATE_FORMAT(time, " +
            "<choose>" +
            "  <when test='granularity == \"day\"'> '%Y-%m-%d' </when>" +
            "  <when test='granularity == \"month\"'> '%Y-%m' </when>" +
            "  <when test='granularity == \"year\"'> '%Y' </when>" +
            "  <otherwise> '%Y-%m-%d' </otherwise>" +
            "</choose> ) AS period, COUNT(*) AS count " +
            "FROM house " +
            "WHERE time BETWEEN #{startDate} AND #{endDate} " +
            "GROUP BY period ORDER BY period" +
            "</script>")
    List<Map<String, Object>> selectTrendStats(@Param("startDate") String startDate,
                                               @Param("endDate") String endDate,
                                               @Param("granularity") String granularity);

    /**
     * 商圈
     * 查询房源分布统计统计分布
     * @return
     */
    @Select("SELECT business_area AS area, COUNT(*) AS count " +
            "FROM house " +
            "WHERE business_area IS NOT NULL AND business_area <> '' " +
            "GROUP BY business_area ORDER BY count DESC")
    List<Map<String, Object>> selectDistributionByBusinessArea();

    /**
     * 地铁站
     * 查询房源分布统计
     * @return
     */
    @Select("SELECT subway_station AS station, COUNT(*) AS count " +
            "FROM house " +
            "WHERE subway_station IS NOT NULL AND subway_station <> '' " +
            "GROUP BY subway_station ORDER BY count DESC")
    List<Map<String, Object>> selectDistributionBySubwayStation();
}

